package bibbletex.controllers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

import bibbletex.helpers.MailHelper;
import bibbletex.helpers.SQLHelper;
import bibbletex.models.Collaboration;
import bibbletex.models.Reference;

public class CollaborationController {
	/**
	 * This method saves, or updates if it's already present, a given Collaboration in the database
	 * 
	 * WARNING: This method does not save the references.
	 * 
	 * @param c Collaboration to save / update
	 * @return Whether the operation was successful
	 */
	public static boolean save(Collaboration c) {
		ResultSet rs = null;
		boolean success = false;

		// if the collaboration exists, update, if not, insert
		rs = SQLHelper.execQuerySQL("select * from collaboration where id = '"
				+ c.getId() + "'");
		try {
			if (rs.next()) {
				// Update
				success = SQLHelper.execSQL("update collaboration set id = '"
						+ c.getId() 
						+ "', public_id = '" + c.getPublic_id()
						+ "', title = '" + c.getTitle()
						+ "', description = '" + c.getDescription()
						+ "', creator_name = '" + c.getCreator_name()
						+ "', email = '" + c.getEmail() + "' where id = '"
						+ c.getId() + "'");
			} else {
				// Insert
				success = SQLHelper
						.execSQL("insert into collaboration (id,public_id,title,description,creator_name,email) values('"
								+ c.getId() + "', '" 
								+ c.getPublic_id() + "', '"
								+ c.getTitle() + "', '"
								+ c.getDescription() + "', '"
								+ c.getCreator_name() + "', '" 
								+ c.getEmail()
								+ "')");
			}
			updateReferences(c);
		} catch (SQLException e) {
			System.err.println("Collaboration.save: " + e);
		}

		return success;
	}

	/**
	 * Deletes a collaboration from the database and all the references
	 * associated
	 * 
	 * @param c
	 *            Collaboration
	 * @return Whether it was successful
	 */
	public static boolean delete(Collaboration c) {
		boolean success = true;
		ResultSet rs = null;
		
		// get all associations
		rs = SQLHelper
				.execQuerySQL("select reference_id from collaboration_reference where collaboration_id='"
						+ c.getId() + "'");

		try {
			// delete all associated references
			while (rs.next() && success)
				success = ReferenceController.delete(rs.getString("reference_id"));

			// delete all associations
			if (rs.next() && success)
				success = SQLHelper
						.execSQL("delete from collaboration_reference where collaboration_id='"
								+ c.getId() + "'");

		} catch (SQLException e) {
			System.err.println("Collaboration.delete: "+e);
		}

		// Now that all the dependences have been deleted, delete the
		// collaboration
		if (success)
			success = SQLHelper.execSQL("delete from collaboration where id='"
					+ c.getId() + "'");

		return success;
	}

	public static boolean deleteReference(Collaboration c, String reference_id) {
		boolean success;

		// delete the associated reference
		success = ReferenceController.delete(reference_id);

		// delete the association
		if (success)
			success = SQLHelper
					.execSQL("delete from collaboration_reference where reference_id='"
							+ reference_id + "'");
		
		return success;
	}
	
	public static boolean addReference(Collaboration c, Reference r) {
		ResultSet rs = null;
		boolean success = false;
		
		ReferenceController.save(r);

		// if the collaboration exists, update, if not, insert
		rs = SQLHelper.execQuerySQL("select * from collaboration_reference where collaboration_id = '"
				+ c.getId() + "' and reference_id ='" + r.getId() + "'");
		try {
			if (!rs.next()) {
				// Insert
				success = SQLHelper
						.execSQL("insert into collaboration_reference values('"
								+ c.getId() + "', '" + r.getId() + "')");
			}
		} catch (SQLException e) {
			System.err.println("Collaboration.save: " + e);
		}

		return success;
	}
	
	public static boolean updateReferences(Collaboration c) {
		boolean success = false;
		ResultSet rs = null;
		
		// get all associations
		rs = SQLHelper
				.execQuerySQL("select reference_id from collaboration_reference where collaboration_id='"
						+ c.getId() + "'");

		try {
			// from all the associated references in the database,
			while (rs.next() && success) {
				// if the collaboration doesn't contain the reference any more, delete it
				if (c.getAllReferences().contains(new Reference(UUID.fromString(rs.getString("reference_id")))))
					deleteReference(c, rs.getString("reference_id"));
				else
				// if it still does, add / update it
					addReference(c, c.getAllReferences().get(
							c.getAllReferences().indexOf(
									new Reference(UUID.fromString(rs.getString("reference_id"))))));
			}
		} catch (SQLException e) {
			System.err.println("Collaboration.updateReferences: " + e);
		}
		
		return success;
	}

	/**
	 * Load the Collaboration with the given ID from the database 
	 * @param id UUID from the Collaboration
	 * @return Loaded Collaboration
	 */
	public static Collaboration load(String id) {
		Collaboration c = null;
		ResultSet rs = null;
		String id_field;
		
		if (Collaboration.id_is_private(id))
			id_field = new String("id");
		else
			id_field = new String("public_id");
		
		try {
			rs = SQLHelper
					.execQuerySQL("select * from collaboration where " + id_field + "='" + id
							+ "'");

			if (rs.next()) {
				c = new Collaboration(UUID.fromString(rs.getString("id")), rs.getString("public_id"));
				c.setTitle(rs.getString("title"));
				c.setDescription(rs.getString("description"));
				c.setCreator_name(rs.getString("creator_name"));
				c.setEmail(rs.getString("email"));

				// Load the references
				ResultSet col_refs = null;

				col_refs = SQLHelper
						.execQuerySQL("select * from collaboration_reference where collaboration_id='"
								+ c.getId() + "'");

				while (col_refs.next()) {
					Reference r = ReferenceController.load(col_refs
							.getString("reference_id"));
					if (r != null)
						c.addReference(r);
				}
			}
		} catch (SQLException e) {
			System.err.println("Collaboration.load: " + e);
		}

		return c;
	}
	
	/**
	 * Invite someone to collaborate per email.
	 * @param c Collaboration
	 * @param from Email of the inviting person
	 * @param email_to Email of the recipient
	 * @param private_access Whether to invite with private access or not
	 */
	public static void inviteEmail(Collaboration c, String from, String email_to, boolean private_access) {
		String link;
		if (private_access)
			link = c.getId().toString();
		else
			link = c.getPublic_id();

		String subject = "BibbleTeX research invitation";
		String messageText = "<h1>Hello!</h1>" + 
							 "<p>You have been invited by " + from + " to collaborate!</p>" +
							 "<p>The ID of the collaboration is"+link+" </p>" +
							 "<p>The BibbleTeX Team</p>";
		MailHelper.sendMail(email_to, subject, messageText);
	}
	
	/**
	 * Collaboration creation email.
	 * @param c Collaboration
	 */
	public static void inviteEmail(Collaboration c) {		
		String subject = "BibbleTeX research collaboration created!";
		String messageText = "<h1>Hello!</h1>" + 
							 "<p>Your collaboration has been correctly created!</p>" +
							 "<p>The private ID of your collaboration is: "+c.getId().toString()+" </p>" +
							 "<p>The BibbleTeX Team</p>";
		MailHelper.sendMail(c.getEmail(), subject, messageText);
	}
}
